<?php


	namespace app\services\admin;


	use app\libs\exceptions\AdminException;
	use PhpOffice\PhpSpreadsheet\IOFactory;
	use PhpOffice\PhpSpreadsheet\Spreadsheet;
	use think\facade\Filesystem;

	class ExcelServices
	{
		/**
		 * 通话列表处导出excel表
		 *
		 * @param $data
		 *
		 * @param
		 *
		 * @return string
		 * @throws
		 *
		 */
		public function exportCommunicationLog ($data) {
			$header = ['通话ID', '电话号码', '归属地', '开始时间', '结束时间'];
			$list = [];
			foreach ($data as $v) {
				$list[] = [
					'id'         => $v['id'],
					'tel'        => $v['tel'],
					'city'       => $v['city'],
					'start_time' => $v['start_time'],
					'end_time'   => $v['end_time'],
				];
			}

			return self::export($header, $list, '通话');

		}

		/**
		 * 通话详情导出excel表
		 *
		 * @param $list
		 *
		 * @param
		 *
		 * @return string
		 * @throws
		 *
		 */
		public function exportCommunicationDetail ($data) {
			$header = ['ID', '电话', '问题', '回复', '会话来源', '审核状态', '评价状态'];
			$list = [];
			foreach ($data as $v) {
				$list[] = [
					'id'        => $v['id'],
					'tel'       => $v['tel'],
					'question'  => $v['question'],
					'answer'    => $v['answer']['ans_text'],
					'origin'    => ($v['origin'] == 1 ? '缺省' : ($v['origin'] == 2 ? '电话机器人' : '文字机器人')),
					'checked'   => ($v['checked'] == 1 ? '未审核' : ($v['checked'] == 2 ? '审核通过' : '审核驳回')),
					'evaluated' => ($v['evaluated'] == 1 ? '未评价' : ($v['evaluated'] == 2 ? '有用' : '无用')),
				];
			}

			return self::export($header, $list, '会话详情');
		}

		/**
		 * 导出意图
		 *
		 * @param $data
		 *
		 * @param
		 *
		 * @return string
		 * @throws
		 *
		 */
		public function exportIntentList ($data) {

			$header = ['类别CODE', '意图CODE', '意图名称', '回复', '是否默认'];

			$list = [];
			foreach ($data as $v) {
				$e = [
					'cate_code'   => $v['cate_code'],
					'intent_code' => $v['intent_code'],
					'intent_name' => $v['intent_name'],
					'reply'       => '',
					'is_default'  => 1
				];

				$list[] = $e;
			}

			return self::export($header, $list, '添加回复');
		}

		/**
		 * 导出短信记录
		 *
		 * @param $data
		 *
		 * @param
		 *
		 * @return string
		 * @throws
		 *
		 */
		public function exportTinreeLog ($data) {
			$header = ['ID', '手机号', '发送内容', '状态', '发送时间'];
			$list = [];
			foreach ($data as $v) {
				$list[] = [
					'id'        => $v['id'],
					'mobile'    => $v['mobile'],
					'content'   => $v['content'],
					'status'    => $v['status'] == 1 ? '成功' : '失败',
					'save_time' => $v['save_time'],
				];
			}

			return self::export($header, $data, '导出数据');
		}

		/**
		 * excel导出数据
		 *
		 * @param        $header            表头
		 * @param        $data              导出数据
		 * @param string $filename          导出文件名
		 * @param string $format            导出文件格式
		 * @param string $title             工作区名
		 *
		 * @param
		 *
		 * @return string
		 * @throws
		 *
		 */
		protected static function export ($header, $data, $filename = '导出数据', $format = 'Xlsx', $title = 'Worksheet') {
			set_time_limit(0);
			$title_key = [];
			foreach ($data[0] as $key => $val) {
				$title_key[] = $key;
			}

			$unit = [
				'A',
				'B',
				'C',
				'D',
				'E',
				'F',
				'G',
				'H',
				'I',
				'J',
				'K',
				'L',
				'M',
				'N',
				'O',
				'P',
				'Q',
				'R',
				'S',
				'T',
				'U',
				'V',
				'W',
				'X',
				'Y',
				'Z',
				'AA',
				'AB',
				'AC',
				'AD',
				'AE',
				'AF',
				'AG',
				'AH',
				'AI',
				'AJ',
				'AK',
				'AL',
				'AM',
				'AN',
				'AO',
				'AP',
				'AQ',
				'AR',
				'AS',
				'AT',
				'AU',
				'AV',
				'AW',
				'AX',
				'AY',
				'AZ'
			];
			$line_count = count($header);

			$newExcel = new Spreadsheet();  //创建一个新的excel文档
			$objSheet = $newExcel->getActiveSheet();  //获取当前操作sheet的对象
			$objSheet->setTitle($title);  //设置当前sheet的标题


			$n = 1;
			for ($i = 0; $i < $line_count; ++$i) {
				$newExcel->getActiveSheet()->getColumnDimension($unit[$i])->setWidth(28);
				$objSheet->setCellValue($unit[$i] . $n, $header[$i]);

				foreach ($data as $key => $val) {
					$m = $key + 2;
					$objSheet->setCellValue($unit[$i] . $m, $val[$title_key[$i]]);
				}
			}

			ob_end_clean();
			// $format只能为 Xlsx 或 Xls
			if ($format == 'Xlsx') {
				header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
			} elseif ($format == 'Xls') {
				header('Content-Type: application/vnd.ms-excel');
			} else {
				header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
			}

			header("Content-Disposition: attachment;filename=" . $filename . '-' . date('YmdHis') . '.' . strtolower($format));
			header('Cache-Control: max-age=0');
			$writer = IOFactory::createWriter($newExcel, $format);

			$dir = root_path() . 'public';
			$folder = 'excel/' . date('Ymd');

			if (!is_dir($dir . '/' . $folder)) {
				mkdir($dir . '/' . $folder, 0755, true);
			}

			$filename .= date('YmdHis') . '.' . strtolower($format);

			$writer->save($dir . '/' . $folder . '/' . $filename);

			$newExcel->disconnectWorksheets();
			unset($newExcel);

			if (ob_get_length()) {
				ob_end_flush();
			}

			return $folder . '/' . $filename;
		}

		/**
		 * excel数据导入
		 *
		 * @param        $file
		 * @param string $disk
		 * @param string $path
		 *
		 * @param
		 *
		 * @return array
		 * @throws
		 *
		 */
		public function import ($file, $disk = 'public', $path = 'loadExcel') {
			set_time_limit(0);
			#只能上传图片或音频


			$fileExtension = $file->getOriginalExtension();

			#文件格式验证
			$extArray = ['xlsx', 'xls'];
			if (!in_array($fileExtension, $extArray)) {
				throw new AdminException('请输入Excel文件');
			}

			$savename = Filesystem::disk($disk)->putFile('/' . $path, $file);
			$savename = str_replace('\\', '/', $savename);

			//数据读取
			$spreadsheet = IOFactory::load('storage/' . $savename);
			$sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
			//清除标题
			array_shift($sheetData);

			//删除上传的文件
			$path = public_path() . 'storage/' . $savename;
			clearstatcache();;
			if (is_file($path)) {
				unlink($path);
			}

			return $sheetData;
		}


	}